<?php
/**
 * Created by PhpStorm.
 * Author: Shadow
 * Date: 2021/12/14
 * Time: 8:00 上午
 * description: Excel.php
 */


namespace app\admin\library;

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use think\exception\ValidateException;
use think\facade\Filesystem;

class Excel
{
    /**
     * @param string $filename
     * @return array|string
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     */
    public static function importExcel($filename = "")
    {
        $file[] = $filename;
        try {
            // 验证文件大小，名称等是否正确
            validate(['file' => 'filesize:51200|fileExt:xls,xlsx'])
                ->check($file);
            // 将文件保存到本地
            $savename = Filesystem::disk('public')->putFile('file', $file[0]);
            // 截取后缀
            $fileExtendName = substr(strrchr($savename, '.'), 1);
            // 有Xls和Xlsx格式两种
            if ($fileExtendName == 'xlsx') {
                $objReader = IOFactory::createReader('Xlsx');
            } else {
                $objReader = IOFactory::createReader('Xls');
            }
            // 设置文件为只读
            $objReader->setReadDataOnly(TRUE);
            // 读取文件，tp6默认上传的文件，在runtime的相应目录下，可根据实际情况自己更改
            $objPHPExcel = $objReader->load(public_path() . 'storage/' . $savename);
            //excel中的第一张sheet
            $sheet = $objPHPExcel->getSheet(0);
            // 取得总行数
            $highestRow = $sheet->getHighestRow();
            // 取得总列数
            $highestColumn = $sheet->getHighestColumn();
            Coordinate::columnIndexFromString($highestColumn);
            $lines = $highestRow - 1;
            if ($lines <= 0) {
                return "数据为空数组";
            }
            // 直接取出excle中的数据
            $data = $objPHPExcel->getActiveSheet()->toArray();
            // 删除第一个元素（表头）
            array_shift($data);
            //删除文件
            unlink(public_path() . 'storage/' . $savename);
            // 返回结果
            return $data;
        } catch (ValidateException $e) {
            return $e->getMessage();
        }
    }

    // 导出
    public static function export($header = [], $type = true, $data = [], $fileName = "1910")
    {
        // 实例化类
        $preadsheet = new Spreadsheet();
        // 创建sheet
        $sheet = $preadsheet->getActiveSheet();
        // 循环设置表头数据
        foreach ($header as $k => $v) {
            $sheet->setCellValue($k, $v);
        }
        // 生成数据
        $sheet->fromArray($data, null, "A2");
        // 样式设置
        $sheet->getDefaultColumnDimension()->setWidth(12);
        // 设置下载与后缀
        if ($type) {
            header("Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            $type = "Xlsx";
            $suffix = "xlsx";
        } else {
            header("Content-Type:application/vnd.ms-excel");
            $type = "Xls";
            $suffix = "xls";
        }
        ob_end_clean();//清楚缓存区
        // 激活浏览器窗口
        header("Content-Disposition:attachment;filename=$fileName.$suffix");
        //缓存控制
        header("Cache-Control:max-age=0");
        // 调用方法执行下载
        $writer = IOFactory::createWriter($preadsheet, $type);
        // 数据流
        $writer->save("php://output");
    }
//    public static  function export() {
//        $list = [
//            [
//                'id' => 1,
//                'name' => '字段1'
//            ],
//            [
//                'id' => 2,
//                'name' => '字段1'
//            ]
//        ];
//
//        $title = ['ID', '名称'];
//        // Create new Spreadsheet object
//        $spreadsheet = new Spreadsheet();
//        $sheet = $spreadsheet->getActiveSheet();
//        //表头
//        //设置单元格内容
//        $titCol = 'A';
//        foreach ($title as $key => $value) {
//            // 单元格内容写入
//            $sheet->setCellValue($titCol . '1', $value);
//            $spreadsheet->getActiveSheet()->getColumnDimension($titCol)->setAutoSize(true);
//            $titCol++;
//        }
//        $row = 2; // 从第二行开始
//        foreach ($list as $item) {
//            $dataCol = 'A';
//            // 单元格内容写入
//            $sheet->setCellValue($dataCol . $row, $item['id']);$dataCol++;
//            $sheet->setCellValue($dataCol . $row, $item['name']);$dataCol++;
//
//            $row++;
//        }
//
//        $name = 'export-'.date('YmdHi');
//        header('Content-Type: application/vnd.ms-excel');
//        header('Content-Disposition: attachment;filename="' . $name . '.xlsx"');
//        header('Cache-Control: max-age=0');
//        $writer = new Xlsx($spreadsheet);
//        $writer->save('php://output');
//        //删除清空：
//        $spreadsheet->disconnectWorksheets();
//
//        $response =  array(
//            'code' => '1',
//            'file' => "data:application/vnd.ms-excel;base64,".base64_encode($xlsData),
//            'msg'=>'导出成功'
//        );
//        return json_encode($response);
//
//    }
    /**
     * 导出excel表方法封装
     * $data：要导出excel表的数据，接受一个二维数组
     * $name：excel表的表名
     * $head：excel表的表头，接受一个一维数组
     * $key：$data中对应表头的键的数组，接受一个一维数组
     * 备注：此函数缺点是，表头（对应列数）不能超过26；
     *循环不够灵活，一个单元格中不方便存放两个数据库字段的值
     */
    public static function exportExcel($name, $data=[], $head=[], $keys=[]){
        $count = count($head);  //计算表头数量
        $spreadsheet = new Spreadsheet();

        $sheet = $spreadsheet->getActiveSheet();

        for ($i = 65; $i < $count + 65; $i++) {     //数字转字母从65开始，循环设置表头：
            $sheet->setCellValue(strtoupper(chr($i)) . '1', $head[$i - 65]);
        }
        /*--------------开始从数据库提取信息插入Excel表中------------------*/
        foreach ($data as $key => $item) {             //循环设置单元格：
            //$key+2,因为第一行是表头，所以写到表格时   从第二行开始写

            for ($i = 65; $i < $count + 65; $i++) {     //数字转字母从65开始：
                $sheet->setCellValue(strtoupper(chr($i)) . ($key + 2), $item[$keys[$i - 65]]);
                $spreadsheet->getActiveSheet()->getColumnDimension(strtoupper(chr($i)))->setWidth(20); //固定列宽
            }

        }

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name . '.xlsx"');
        header('Cache-Control: max-age=0');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

        header('Data-Type: binary');
        //前端导出数据根据这个unicode格式解析为中文
        header('Data-Filename: ' . $str);
        header('Content-Disposition: attachment;filename="' . $names . '.xlsx"');
        header('Cache-Control: max-age=0');
        header('Access-Control-Expose-Headers:Data-Type,Data-Filename');

        $writer = new Xlsx($spreadsheet);
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');

        ob_start(); //打开缓冲区
        $writer->save('php://output'); //这里就是乱码
        $xlsData = ob_get_contents();
        ob_end_clean(); // 清除缓冲

        // 接收$writer->save('php://output')
        $response =  array(
            'code' => '1',
            'file' => "data:application/vnd.ms-excel;base64,".base64_encode($xlsData),
            'msg'=>'导出成功'
        );
        return json_encode($response);
    }
    public static function downloadExcel($name, $data = [], $head = [], $keys = [])
    {
        $count = count($head);  //计算表头数量
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        for ($i = 65; $i < $count + 65; $i++) {     //数字转字母从65开始，循环设置表头：
            $sheet->setCellValue(strtoupper(chr($i)) . '1', $head[$i - 65]);
        }
        //循环设置单元格：
        foreach ($data as $key => $item) {
            //$key+2,因为第一行是表头，所以写到表格时   从第二行开始写
            for ($i = 65; $i < $count + 65; $i++) {
                //数字转字母从65开始：
                $sheet->setCellValue(strtoupper(chr($i)) . ($key + 2), $item[$keys[$i - 65]]);
                //固定列宽
                $spreadsheet->getActiveSheet()->getColumnDimension(strtoupper(chr($i)))->setWidth(20);
            }

        }
        $names = $name;
        //utf-8转unicode格式
        $name = iconv('UTF-8', 'UCS-2BE', $name);
        $len = strlen($name);

        $str = '';

        for ($i = 0; $i < $len - 1; $i = $i + 2) {

            $c = $name[$i];

            $c2 = $name[$i + 1];

            if (ord($c) > 0) {

                $str .= '\u' . base_convert(ord($c), 10, 16) . str_pad(base_convert(ord($c2), 10, 16), 2, 0, STR_PAD_LEFT);

            } else {

                $str .= '\u' . str_pad(base_convert(ord($c2), 10, 16), 4, 0, STR_PAD_LEFT);

            }

        }

//        header('Content-Type: application/vnd.ms-excel');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

        header('Data-Type: binary');
        //前端导出数据根据这个unicode格式解析为中文
        header('Data-Filename: ' . $str);
        header('Content-Disposition: attachment;filename="' . $names . '.xlsx"');
        header('Cache-Control: max-age=0');
        header('Access-Control-Expose-Headers:Data-Type,Data-Filename');

        //header('Content-Type: application/vnd.ms-excel');
        //header('Content-Disposition: attachment;filename="' . $name . '.xlsx"');
        //header('Cache-Control: max-age=0');
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;

    }

}

